返回首页

Burgeon 解决表空间不足

本文档记录了 Burgeon 项目中涉及表空间操作的相关 SQL,包括查询数据文件、添加数据文件以及查看表空间使用详情等。

一、查询数据文件信息

SELECT * FROM dba_data_files;

二、添加数据文件(自动扩展)

ALTER TABLESPACE bosnds3 ADD DATAFILE '/data/oradata/orcl/bosnds3_03.DBFF' SIZE 10M AUTOEXTEND ON;

三、查询表空间使用详情

SELECT d.tablespace_name                                              NAME,
       d.status                                                       STATUS,
       file_cnt,
       auto_file_cnt,
       max_create,
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.90')       size_mb,
       TO_CHAR(NVL(a.total - NVL(f.bytes, 0), 0) / 1024 / 1024, '99999999.99') use_mb,
       ROUND((NVL(a.bytes, 0) - NVL(a.total - NVL(f.bytes, 0), 0)) / 1024 / 1024, 2) free_mb,
       TO_CHAR(NVL((a.total - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00')       used_lv
FROM   sys.dba_tablespaces d,
       (SELECT a.tablespace_name,
               SUM((DECODE(a.autoextensible, 'YES',
                           CASE
                             WHEN a.autoextensible = 'YES' AND a.maxbytes >= a.bytes THEN a.maxbytes
                             ELSE a.bytes
                           END, 0) + DECODE(autoextensible, 'NO', a.bytes, 0))) bytes,
               COUNT(1)                                                file_cnt,
               COUNT(CASE WHEN a.autoextensible = 'YES' THEN 1 END)    auto_file_cnt,
               MAX(b.creation_time)                                    max_create,
               SUM(a.bytes)                                            total
        FROM   dba_data_files a, v$datafile b
        WHERE  a.file_id = b.file#
        GROUP  BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes
        FROM   dba_free_space
        GROUP  BY tablespace_name) f
WHERE  d.tablespace_name = a.tablespace_name(+)
  AND  d.tablespace_name = f.tablespace_name(+)
  AND  NOT (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY');